package cn.edu.sanxiau.www.dao.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sanxiau.www.dao.PermissionDao;
import cn.edu.sanxiau.www.model.Permission;
import cn.edu.sanxiau.www.model.Role;
import frame.utils.dbutil.JdbcUtils;

public class PermissionDaoImpl implements PermissionDao {

	@Override
	public List<Permission> queryAllPermission() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM sys_permission";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			List<Permission> permission_db_list = new ArrayList<Permission>();

			while (rs.next()) {
				Permission permission_db = new Permission();
				permission_db.setPermissionId(rs.getInt("permissionId"));
				permission_db.setpId(rs.getInt("pId"));
				permission_db.setName(rs.getString("name"));
				permission_db.setType(rs.getString("type"));
				permission_db.setUrl(rs.getString("url"));
				permission_db.setState(rs.getString("state"));

				permission_db_list.add(permission_db);
			}

			return permission_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int addPermissionByPermission(Permission permission) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "INSERT INTO  sys_permission (permissionId,pId,name,type,url,state) VALUES(?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, permission.getPermissionId());
			pstmt.setInt(2, permission.getpId());
			pstmt.setString(3, permission.getName());
			pstmt.setString(4, permission.getType());
			pstmt.setString(5, permission.getUrl());
			pstmt.setString(6, permission.getState());

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deletePermissionByPermissionId(int permissionId) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();

			String sql = "DELETE FROM sys_permission WHERE permissionId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, permissionId);

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public Permission queryPermissionByPermissionId(int permissionId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM sys_permission WHERE permissionId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, permissionId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Permission permission_db = new Permission();
				permission_db.setPermissionId(rs.getInt("permissionId"));
				permission_db.setpId(rs.getInt("pId"));
				permission_db.setName(rs.getString("name"));
				permission_db.setType(rs.getString("type"));
				permission_db.setUrl(rs.getString("url"));
				permission_db.setState(rs.getString("state"));
				return permission_db;
			}
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Permission queryPermissionBypId(int pId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM sys_permission WHERE permissionId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, pId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Permission permission_db = new Permission();
				permission_db.setPermissionId(rs.getInt("permissionId"));
				permission_db.setpId(rs.getInt("pId"));
				permission_db.setName(rs.getString("name"));
				permission_db.setType(rs.getString("type"));
				permission_db.setUrl(rs.getString("url"));
				permission_db.setState(rs.getString("state"));
				return permission_db;
			}
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int updatePermissionByPermission(Permission permission) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "UPDATE sys_permission SET pId=?,name=?,type=?,url=?,state=? WHERE permissionId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, permission.getpId());
			pstmt.setString(2, permission.getName());
			pstmt.setString(3, permission.getType());
			pstmt.setString(4, permission.getUrl());
			pstmt.setString(5, permission.getState());
			pstmt.setInt(6, permission.getPermissionId());
			int m = pstmt.executeUpdate();
			return m;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<Permission> queryPermissionByroleId(int roleId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT sys_permission.permissionId, sys_permission.pId,sys_permission.`name`,sys_permission.type,sys_permission.url,sys_permission.state FROM sys_role_permission INNER JOIN sys_permission ON sys_role_permission.permissionId = sys_permission.permissionId WHERE roleId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roleId);
			rs = pstmt.executeQuery();

			List<Permission> permission_db_list = new ArrayList<Permission>();

			while (rs.next()) {
				Permission permission_db = new Permission();
				permission_db.setPermissionId(rs.getInt("permissionId"));
				permission_db.setpId(rs.getInt("pId"));
				permission_db.setName(rs.getString("name"));
				permission_db.setType(rs.getString("type"));
				permission_db.setUrl(rs.getString("url"));
				permission_db.setState(rs.getString("state"));

				permission_db_list.add(permission_db);
			}

			return permission_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Permission> queryAllPermissionByUserId(int userId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT sys_permission.permissionId,sys_permission.pId,sys_permission.`name`,sys_permission.type,sys_permission.url,sys_permission.state FROM sys_user_role INNER JOIN sys_role ON sys_user_role.roleId = sys_role.roleId INNER JOIN sys_role_permission ON sys_role.roleId = sys_role_permission.roleId INNER JOIN sys_permission ON sys_role_permission.permissionId = sys_permission.permissionId WHERE userId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, userId);
			rs = pstmt.executeQuery();

			List<Permission> permission_db_list = new ArrayList<Permission>();

			while (rs.next()) {
				Permission permission_db = new Permission();
				permission_db.setPermissionId(rs.getInt("permissionId"));
				permission_db.setpId(rs.getInt("pId"));
				permission_db.setName(rs.getString("name"));
				permission_db.setType(rs.getString("type"));
				permission_db.setUrl(rs.getString("url"));
				permission_db.setState(rs.getString("state"));

				permission_db_list.add(permission_db);
			}

			return permission_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Permission> queryUser_xz_permissionByUserId(int userId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT sys_permission.permissionId,sys_permission.pId,sys_permission.`name`,sys_permission.type,sys_permission.url,sys_permission.state FROM sys_user_role INNER JOIN sys_role ON sys_user_role.roleId = sys_role.roleId INNER JOIN sys_role_permission ON sys_role.roleId = sys_role_permission.roleId INNER JOIN sys_permission ON sys_role_permission.permissionId = sys_permission.permissionId WHERE userId=?  AND type='菜单'";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, userId);
			rs = pstmt.executeQuery();

			List<Permission> permission_db_list = new ArrayList<Permission>();

			while (rs.next()) {
				Permission permission_db = new Permission();
				permission_db.setPermissionId(rs.getInt("permissionId"));
				permission_db.setpId(rs.getInt("pId"));
				permission_db.setName(rs.getString("name"));
				permission_db.setType(rs.getString("type"));
				permission_db.setUrl(rs.getString("url"));
				permission_db.setState(rs.getString("state"));

				permission_db_list.add(permission_db);
			}

			return permission_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Permission> queryXZPermissionAllSonByPId(int pId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM sys_permission WHERE pId =?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, pId);
			rs = pstmt.executeQuery();

			List<Permission> permission_db_list = new ArrayList<Permission>();

			while (rs.next()) {
				Permission permission_db = new Permission();
				permission_db.setPermissionId(rs.getInt("permissionId"));
				permission_db.setpId(rs.getInt("pId"));
				permission_db.setName(rs.getString("name"));
				permission_db.setType(rs.getString("type"));
				permission_db.setUrl(rs.getString("url"));
				permission_db.setState(rs.getString("state"));

				permission_db_list.add(permission_db);
			}

			return permission_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
